﻿USE Kiosk
/***********************************************************
*
* CRUD Procedures for [dbo].[CellProvider] table
*
************************************************************/
PRINT 'Creating stored procedures for CellProvider table'
-- Insert CellProvider
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[InsertCellProvider]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[InsertCellProvider]
END

DECLARE @insertCellProviderProc varchar(max)
SET @insertCellProviderProc = 
'CREATE PROCEDURE [dbo].[InsertCellProvider]
(
	@CellProviderID uniqueidentifier,
	@Name varchar(50),
	@SmsGateway varchar(100),
	@newTimestamp timestamp output
)
AS
BEGIN
	INSERT INTO CellProvider
	(
		[CellProviderID],
		[Name],
		[SmsGateway]
	) 
	VALUES 
	(
		@CellProviderID,
		@Name,
		@SmsGateway
	)

	SELECT @newTimestamp = Timestamp 
	FROM CellProvider WHERE  [CellProviderID] = @CellProviderID 
	RETURN
END'
exec(@insertCellProviderProc)

-- Delete CellProvider
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DeleteCellProvider]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[DeleteCellProvider]
END

DECLARE @deleteCellProviderProc varchar(max)
SET @deleteCellProviderProc = 
'CREATE PROCEDURE [dbo].[DeleteCellProvider]
(
	@CellProviderID uniqueidentifier
)
AS
BEGIN
	DELETE CellProvider
	WHERE CellProviderID=@CellProviderID
	RETURN
END'
exec(@deleteCellProviderProc)

-- Update CellProvider
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[UpdateCellProvider]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[UpdateCellProvider]
END

DECLARE @updateCellProviderProc varchar(max)
SET @updateCellProviderProc = 
'CREATE PROCEDURE [dbo].[UpdateCellProvider]
(
	@CellProviderID uniqueidentifier,
	@Name varchar(50),
	@SmsGateway varchar(100),
	@Timestamp timestamp,
	@newTimestamp timestamp output
)
AS
BEGIN
	UPDATE CellProvider 
	SET 
		[Name] = @Name,
		[SmsGateway] = @SmsGateway
	WHERE  [CellProviderID] = @CellProviderID 
	  AND Timestamp = @TimeStamp

  IF @@ROWCOUNT = 0
    RAISERROR(''Row has been edited by another user'', 16, 1)            	

	SELECT @newTimeStamp = Timestamp
	FROM CellProvider WHERE  [CellProviderID] = @CellProviderID 
	RETURN 
END'
exec(@updateCellProviderProc)

-- Fetch CellProvider
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[FetchCellProvider]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[FetchCellProvider]
END

DECLARE @fetchCellProviderProc varchar(max)
SET @fetchCellProviderProc = 
'CREATE PROCEDURE [dbo].[FetchCellProvider]
(
	@CellProviderID uniqueidentifier
)
AS
BEGIN
	SELECT 
		[CellProviderID],
		[Name],
		[SmsGateway],
		[Timestamp]	
	FROM CellProvider 
	WHERE  [CellProviderID] = @CellProviderID 
	RETURN 
END'
exec(@fetchCellProviderProc)

/***********************************************************
*
* CRUD Procedures for [dbo].[Employee] table
*
************************************************************/
PRINT 'Creating stored procedures for Employee table'
-- Insert Employee
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[InsertEmployee]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[InsertEmployee]
END

DECLARE @insertEmployeeProc varchar(max)
SET @insertEmployeeProc = 
'CREATE PROCEDURE [dbo].[InsertEmployee]
(
	@EmployeeID uniqueidentifier,
	@DomainName varchar(150),
	@FirstName varchar(50),
	@LastName varchar(50),
	@LocationID uniqueidentifier,
	@Email varchar(150),
	@OfficePhone varchar(15),
	@CellPhone varchar(15),
	@CellProviderID uniqueidentifier,
	@newTimestamp timestamp output
)
AS
BEGIN
	INSERT INTO Employee
	(
		[EmployeeID],
		[DomainName],
		[FirstName],
		[LastName],
		[LocationID],
		[Email],
		[OfficePhone],
		[CellPhone],
		[CellProviderID]
	) 
	VALUES 
	(
		@EmployeeID,
		@DomainName,
		@FirstName,
		@LastName,
		@LocationID,
		@Email,
		@OfficePhone,
		@CellPhone,
		@CellProviderID
	)

	SELECT @newTimestamp = Timestamp 
	FROM Employee WHERE  [EmployeeID] = @EmployeeID 
	RETURN
END'
exec(@insertEmployeeProc)

-- Delete Employee
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DeleteEmployee]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[DeleteEmployee]
END

DECLARE @deleteEmployeeProc varchar(max)
SET @deleteEmployeeProc = 
'CREATE PROCEDURE [dbo].[DeleteEmployee]
(
	@EmployeeID uniqueidentifier
)
AS
BEGIN
	DELETE Employee
	WHERE EmployeeID=@EmployeeID
	RETURN
END'
exec(@deleteEmployeeProc)

-- Update Employee
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[UpdateEmployee]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[UpdateEmployee]
END

DECLARE @updateEmployeeProc varchar(max)
SET @updateEmployeeProc = 
'CREATE PROCEDURE [dbo].[UpdateEmployee]
(
	@EmployeeID uniqueidentifier,
	@DomainName varchar(150),
	@FirstName varchar(50),
	@LastName varchar(50),
	@LocationID uniqueidentifier,
	@Email varchar(150),
	@OfficePhone varchar(15),
	@CellPhone varchar(15),
	@CellProviderID uniqueidentifier,
	@Timestamp timestamp,
	@newTimestamp timestamp output
)
AS
BEGIN
	UPDATE Employee 
	SET 
		[DomainName] = @DomainName,
		[FirstName] = @FirstName,
		[LastName] = @LastName,
		[LocationID] = @LocationID,
		[Email] = @Email,
		[OfficePhone] = @OfficePhone,
		[CellPhone] = @CellPhone,
		[CellProviderID] = @CellProviderID
	WHERE  [EmployeeID] = @EmployeeID 
	  AND Timestamp = @TimeStamp

  IF @@ROWCOUNT = 0
    RAISERROR(''Row has been edited by another user'', 16, 1)            	

	SELECT @newTimeStamp = Timestamp
	FROM Employee WHERE  [EmployeeID] = @EmployeeID 
	RETURN 
END'
exec(@updateEmployeeProc)

-- Fetch Employee
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[FetchEmployee]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[FetchEmployee]
END

DECLARE @fetchEmployeeProc varchar(max)
SET @fetchEmployeeProc = 
'CREATE PROCEDURE [dbo].[FetchEmployee]
(
	@EmployeeID uniqueidentifier
)
AS
BEGIN
	SELECT 
		[EmployeeID],
		[DomainName],
		[FirstName],
		[LastName],
		[LocationID],
		[Email],
		[OfficePhone],
		[CellPhone],
		[CellProviderID],
		[Timestamp]	
	FROM Employee 
	WHERE  [EmployeeID] = @EmployeeID 
	RETURN 
END'
exec(@fetchEmployeeProc)

/***********************************************************
*
* CRUD Procedures for [dbo].[Icon] table
*
************************************************************/
PRINT 'Creating stored procedures for Icon table'
-- Insert Icon
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[InsertIcon]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[InsertIcon]
END

DECLARE @insertIconProc varchar(max)
SET @insertIconProc = 
'CREATE PROCEDURE [dbo].[InsertIcon]
(
	@IconID uniqueidentifier,
	@IconFile varchar(1024),
	@IconKeyword varchar(50),
	@newTimestamp timestamp output
)
AS
BEGIN
	INSERT INTO Icon
	(
		[IconID],
		[IconFile],
		[IconKeyword]
	) 
	VALUES 
	(
		@IconID,
		@IconFile,
		@IconKeyword
	)

	SELECT @newTimestamp = Timestamp 
	FROM Icon WHERE  [IconID] = @IconID 
	RETURN
END'
exec(@insertIconProc)

-- Delete Icon
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DeleteIcon]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[DeleteIcon]
END

DECLARE @deleteIconProc varchar(max)
SET @deleteIconProc = 
'CREATE PROCEDURE [dbo].[DeleteIcon]
(
	@IconID uniqueidentifier
)
AS
BEGIN
	DELETE Icon
	WHERE IconID=@IconID
	RETURN
END'
exec(@deleteIconProc)

-- Update Icon
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[UpdateIcon]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[UpdateIcon]
END

DECLARE @updateIconProc varchar(max)
SET @updateIconProc = 
'CREATE PROCEDURE [dbo].[UpdateIcon]
(
	@IconID uniqueidentifier,
	@IconFile varchar(1024),
	@IconKeyword varchar(50),
	@Timestamp timestamp,
	@newTimestamp timestamp output
)
AS
BEGIN
	UPDATE Icon 
	SET 
		[IconFile] = @IconFile,
		[IconKeyword] = @IconKeyword
	WHERE  [IconID] = @IconID 
	  AND Timestamp = @TimeStamp

  IF @@ROWCOUNT = 0
    RAISERROR(''Row has been edited by another user'', 16, 1)            	

	SELECT @newTimeStamp = Timestamp
	FROM Icon WHERE  [IconID] = @IconID 
	RETURN 
END'
exec(@updateIconProc)

-- Fetch Icon
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[FetchIcon]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[FetchIcon]
END

DECLARE @fetchIconProc varchar(max)
SET @fetchIconProc = 
'CREATE PROCEDURE [dbo].[FetchIcon]
(
	@IconID uniqueidentifier
)
AS
BEGIN
	SELECT 
		[IconID],
		[IconFile],
		[IconKeyword],
		[Timestamp]	
	FROM Icon 
	WHERE  [IconID] = @IconID 
	RETURN 
END'
exec(@fetchIconProc)

/***********************************************************
*
* CRUD Procedures for [dbo].[Location] table
*
************************************************************/
PRINT 'Creating stored procedures for Location table'
-- Insert Location
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[InsertLocation]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[InsertLocation]
END

DECLARE @insertLocationProc varchar(max)
SET @insertLocationProc = 
'CREATE PROCEDURE [dbo].[InsertLocation]
(
	@LocationID uniqueidentifier,
	@Name varchar(50),
	@XCoordinate int,
	@YCoordinate int,
	@newTimestamp timestamp output
)
AS
BEGIN
	INSERT INTO Location
	(
		[LocationID],
		[Name],
		[XCoordinate],
		[YCoordinate]
	) 
	VALUES 
	(
		@LocationID,
		@Name,
		@XCoordinate,
		@YCoordinate
	)

	SELECT @newTimestamp = Timestamp 
	FROM Location WHERE  [LocationID] = @LocationID 
	RETURN
END'
exec(@insertLocationProc)

-- Delete Location
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DeleteLocation]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[DeleteLocation]
END

DECLARE @deleteLocationProc varchar(max)
SET @deleteLocationProc = 
'CREATE PROCEDURE [dbo].[DeleteLocation]
(
	@LocationID uniqueidentifier
)
AS
BEGIN
	DELETE Location
	WHERE LocationID=@LocationID
	RETURN
END'
exec(@deleteLocationProc)

-- Update Location
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[UpdateLocation]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[UpdateLocation]
END

DECLARE @updateLocationProc varchar(max)
SET @updateLocationProc = 
'CREATE PROCEDURE [dbo].[UpdateLocation]
(
	@LocationID uniqueidentifier,
	@Name varchar(50),
	@XCoordinate int,
	@YCoordinate int,
	@Timestamp timestamp,
	@newTimestamp timestamp output
)
AS
BEGIN
	UPDATE Location 
	SET 
		[Name] = @Name,
		[XCoordinate] = @XCoordinate,
		[YCoordinate] = @YCoordinate
	WHERE  [LocationID] = @LocationID 
	  AND Timestamp = @TimeStamp

  IF @@ROWCOUNT = 0
    RAISERROR(''Row has been edited by another user'', 16, 1)            	

	SELECT @newTimeStamp = Timestamp
	FROM Location WHERE  [LocationID] = @LocationID 
	RETURN 
END'
exec(@updateLocationProc)

-- Fetch Location
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[FetchLocation]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[FetchLocation]
END

DECLARE @fetchLocationProc varchar(max)
SET @fetchLocationProc = 
'CREATE PROCEDURE [dbo].[FetchLocation]
(
	@LocationID uniqueidentifier
)
AS
BEGIN
	SELECT 
		[LocationID],
		[Name],
		[XCoordinate],
		[YCoordinate],
		[Timestamp]	
	FROM Location 
	WHERE  [LocationID] = @LocationID 
	RETURN 
END'
exec(@fetchLocationProc)

/***********************************************************
*
* CRUD Procedures for [dbo].[Notification] table
*
************************************************************/
PRINT 'Creating stored procedures for Notification table'
-- Insert Notification
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[InsertNotification]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[InsertNotification]
END

DECLARE @insertNotificationProc varchar(max)
SET @insertNotificationProc = 
'CREATE PROCEDURE [dbo].[InsertNotification]
(
	@NotificationID uniqueidentifier,
	@NotificationTypeID uniqueidentifier,
	@LocationID uniqueidentifier,
	@EmployeeID uniqueidentifier,
	@NotificationDate datetime,
	@Title varchar(50),
	@Description varchar(MAX),
	@newTimestamp timestamp output
)
AS
BEGIN
	INSERT INTO Notification
	(
		[NotificationID],
		[NotificationTypeID],
		[LocationID],
		[EmployeeID],
		[NotificationDate],
		[Title],
		[Description]
	) 
	VALUES 
	(
		@NotificationID,
		@NotificationTypeID,
		@LocationID,
		@EmployeeID,
		@NotificationDate,
		@Title,
		@Description
	)

	SELECT @newTimestamp = Timestamp 
	FROM Notification WHERE  [NotificationID] = @NotificationID 
	RETURN
END'
exec(@insertNotificationProc)

-- Delete Notification
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DeleteNotification]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[DeleteNotification]
END

DECLARE @deleteNotificationProc varchar(max)
SET @deleteNotificationProc = 
'CREATE PROCEDURE [dbo].[DeleteNotification]
(
	@NotificationID uniqueidentifier
)
AS
BEGIN
	DELETE Notification
	WHERE NotificationID=@NotificationID
	RETURN
END'
exec(@deleteNotificationProc)

-- Update Notification
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[UpdateNotification]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[UpdateNotification]
END

DECLARE @updateNotificationProc varchar(max)
SET @updateNotificationProc = 
'CREATE PROCEDURE [dbo].[UpdateNotification]
(
	@NotificationID uniqueidentifier,
	@NotificationTypeID uniqueidentifier,
	@LocationID uniqueidentifier,
	@EmployeeID uniqueidentifier,
	@NotificationDate datetime,
	@Title varchar(50),
	@Description varchar(MAX),
	@Timestamp timestamp,
	@newTimestamp timestamp output
)
AS
BEGIN
	UPDATE Notification 
	SET 
		[NotificationTypeID] = @NotificationTypeID,
		[LocationID] = @LocationID,
		[EmployeeID] = @EmployeeID,
		[NotificationDate] = @NotificationDate,
		[Title] = @Title,
		[Description] = @Description
	WHERE  [NotificationID] = @NotificationID 
	  AND Timestamp = @TimeStamp

  IF @@ROWCOUNT = 0
    RAISERROR(''Row has been edited by another user'', 16, 1)            	

	SELECT @newTimeStamp = Timestamp
	FROM Notification WHERE  [NotificationID] = @NotificationID 
	RETURN 
END'
exec(@updateNotificationProc)

-- Fetch Notification
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[FetchNotification]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[FetchNotification]
END

DECLARE @fetchNotificationProc varchar(max)
SET @fetchNotificationProc = 
'CREATE PROCEDURE [dbo].[FetchNotification]
(
	@NotificationID uniqueidentifier
)
AS
BEGIN
	SELECT 
		[NotificationID],
		[NotificationTypeID],
		[LocationID],
		[EmployeeID],
		[NotificationDate],
		[Title],
		[Description],
		[Timestamp]	
	FROM Notification 
	WHERE  [NotificationID] = @NotificationID 
	RETURN 
END'
exec(@fetchNotificationProc)

/***********************************************************
*
* CRUD Procedures for [dbo].[NotificationType] table
*
************************************************************/
PRINT 'Creating stored procedures for NotificationType table'
-- Insert NotificationType
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[InsertNotificationType]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[InsertNotificationType]
END

DECLARE @insertNotificationTypeProc varchar(max)
SET @insertNotificationTypeProc = 
'CREATE PROCEDURE [dbo].[InsertNotificationType]
(
	@NotificationTypeID uniqueidentifier,
	@Name varchar(50),
	@newTimestamp timestamp output
)
AS
BEGIN
	INSERT INTO NotificationType
	(
		[NotificationTypeID],
		[Name]
	) 
	VALUES 
	(
		@NotificationTypeID,
		@Name
	)

	SELECT @newTimestamp = Timestamp 
	FROM NotificationType WHERE  [NotificationTypeID] = @NotificationTypeID 
	RETURN
END'
exec(@insertNotificationTypeProc)

-- Delete NotificationType
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DeleteNotificationType]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[DeleteNotificationType]
END

DECLARE @deleteNotificationTypeProc varchar(max)
SET @deleteNotificationTypeProc = 
'CREATE PROCEDURE [dbo].[DeleteNotificationType]
(
	@NotificationTypeID uniqueidentifier
)
AS
BEGIN
	DELETE NotificationType
	WHERE NotificationTypeID=@NotificationTypeID
	RETURN
END'
exec(@deleteNotificationTypeProc)

-- Update NotificationType
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[UpdateNotificationType]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[UpdateNotificationType]
END

DECLARE @updateNotificationTypeProc varchar(max)
SET @updateNotificationTypeProc = 
'CREATE PROCEDURE [dbo].[UpdateNotificationType]
(
	@NotificationTypeID uniqueidentifier,
	@Name varchar(50),
	@Timestamp timestamp,
	@newTimestamp timestamp output
)
AS
BEGIN
	UPDATE NotificationType 
	SET 
		[Name] = @Name
	WHERE  [NotificationTypeID] = @NotificationTypeID 
	  AND Timestamp = @TimeStamp

  IF @@ROWCOUNT = 0
    RAISERROR(''Row has been edited by another user'', 16, 1)            	

	SELECT @newTimeStamp = Timestamp
	FROM NotificationType WHERE  [NotificationTypeID] = @NotificationTypeID 
	RETURN 
END'
exec(@updateNotificationTypeProc)

-- Fetch NotificationType
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[FetchNotificationType]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[FetchNotificationType]
END

DECLARE @fetchNotificationTypeProc varchar(max)
SET @fetchNotificationTypeProc = 
'CREATE PROCEDURE [dbo].[FetchNotificationType]
(
	@NotificationTypeID uniqueidentifier
)
AS
BEGIN
	SELECT 
		[NotificationTypeID],
		[Name],
		[Timestamp]	
	FROM NotificationType 
	WHERE  [NotificationTypeID] = @NotificationTypeID 
	RETURN 
END'
exec(@fetchNotificationTypeProc)

/***********************************************************
*
* CRUD Procedures for [dbo].[Presentation] table
*
************************************************************/
PRINT 'Creating stored procedures for Presentation table'
-- Insert Presentation
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[InsertPresentation]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[InsertPresentation]
END

DECLARE @insertPresentationProc varchar(max)
SET @insertPresentationProc = 
'CREATE PROCEDURE [dbo].[InsertPresentation]
(
	@PresentationID uniqueidentifier,
	@PresentationTypeID uniqueidentifier,
	@PresentationStatusID uniqueidentifier,
	@Name varchar(50),
	@newTimestamp timestamp output
)
AS
BEGIN
	INSERT INTO Presentation
	(
		[PresentationID],
		[PresentationTypeID],
		[PresentationStatusID],
		[Name]
	) 
	VALUES 
	(
		@PresentationID,
		@PresentationTypeID,
		@PresentationStatusID,
		@Name
	)

	SELECT @newTimestamp = Timestamp 
	FROM Presentation WHERE  [PresentationID] = @PresentationID 
	RETURN
END'
exec(@insertPresentationProc)

-- Delete Presentation
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DeletePresentation]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[DeletePresentation]
END

DECLARE @deletePresentationProc varchar(max)
SET @deletePresentationProc = 
'CREATE PROCEDURE [dbo].[DeletePresentation]
(
	@PresentationID uniqueidentifier
)
AS
BEGIN
	DELETE Presentation
	WHERE PresentationID=@PresentationID
	RETURN
END'
exec(@deletePresentationProc)

-- Update Presentation
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[UpdatePresentation]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[UpdatePresentation]
END

DECLARE @updatePresentationProc varchar(max)
SET @updatePresentationProc = 
'CREATE PROCEDURE [dbo].[UpdatePresentation]
(
	@PresentationID uniqueidentifier,
	@PresentationTypeID uniqueidentifier,
	@PresentationStatusID uniqueidentifier,
	@Name varchar(50),
	@Timestamp timestamp,
	@newTimestamp timestamp output
)
AS
BEGIN
	UPDATE Presentation 
	SET 
		[PresentationTypeID] = @PresentationTypeID,
		[PresentationStatusID] = @PresentationStatusID,
		[Name] = @Name
	WHERE  [PresentationID] = @PresentationID 
	  AND Timestamp = @TimeStamp

  IF @@ROWCOUNT = 0
    RAISERROR(''Row has been edited by another user'', 16, 1)            	

	SELECT @newTimeStamp = Timestamp
	FROM Presentation WHERE  [PresentationID] = @PresentationID 
	RETURN 
END'
exec(@updatePresentationProc)

-- Fetch Presentation
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[FetchPresentation]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[FetchPresentation]
END

DECLARE @fetchPresentationProc varchar(max)
SET @fetchPresentationProc = 
'CREATE PROCEDURE [dbo].[FetchPresentation]
(
	@PresentationID uniqueidentifier
)
AS
BEGIN
	SELECT 
		[PresentationID],
		[PresentationTypeID],
		[PresentationStatusID],
		[Name],
		[Timestamp]	
	FROM Presentation 
	WHERE  [PresentationID] = @PresentationID 
	RETURN 
END'
exec(@fetchPresentationProc)

/***********************************************************
*
* CRUD Procedures for [dbo].[PresentationStatus] table
*
************************************************************/
PRINT 'Creating stored procedures for PresentationStatus table'
-- Insert PresentationStatus
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[InsertPresentationStatus]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[InsertPresentationStatus]
END

DECLARE @insertPresentationStatusProc varchar(max)
SET @insertPresentationStatusProc = 
'CREATE PROCEDURE [dbo].[InsertPresentationStatus]
(
	@PresentationStatusID uniqueidentifier,
	@Name varchar(50),
	@newTimestamp timestamp output
)
AS
BEGIN
	INSERT INTO PresentationStatus
	(
		[PresentationStatusID],
		[Name]
	) 
	VALUES 
	(
		@PresentationStatusID,
		@Name
	)

	SELECT @newTimestamp = Timestamp 
	FROM PresentationStatus WHERE  [PresentationStatusID] = @PresentationStatusID 
	RETURN
END'
exec(@insertPresentationStatusProc)

-- Delete PresentationStatus
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DeletePresentationStatus]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[DeletePresentationStatus]
END

DECLARE @deletePresentationStatusProc varchar(max)
SET @deletePresentationStatusProc = 
'CREATE PROCEDURE [dbo].[DeletePresentationStatus]
(
	@PresentationStatusID uniqueidentifier
)
AS
BEGIN
	DELETE PresentationStatus
	WHERE PresentationStatusID=@PresentationStatusID
	RETURN
END'
exec(@deletePresentationStatusProc)

-- Update PresentationStatus
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[UpdatePresentationStatus]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[UpdatePresentationStatus]
END

DECLARE @updatePresentationStatusProc varchar(max)
SET @updatePresentationStatusProc = 
'CREATE PROCEDURE [dbo].[UpdatePresentationStatus]
(
	@PresentationStatusID uniqueidentifier,
	@Name varchar(50),
	@Timestamp timestamp,
	@newTimestamp timestamp output
)
AS
BEGIN
	UPDATE PresentationStatus 
	SET 
		[Name] = @Name
	WHERE  [PresentationStatusID] = @PresentationStatusID 
	  AND Timestamp = @TimeStamp

  IF @@ROWCOUNT = 0
    RAISERROR(''Row has been edited by another user'', 16, 1)            	

	SELECT @newTimeStamp = Timestamp
	FROM PresentationStatus WHERE  [PresentationStatusID] = @PresentationStatusID 
	RETURN 
END'
exec(@updatePresentationStatusProc)

-- Fetch PresentationStatus
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[FetchPresentationStatus]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[FetchPresentationStatus]
END

DECLARE @fetchPresentationStatusProc varchar(max)
SET @fetchPresentationStatusProc = 
'CREATE PROCEDURE [dbo].[FetchPresentationStatus]
(
	@PresentationStatusID uniqueidentifier
)
AS
BEGIN
	SELECT 
		[PresentationStatusID],
		[Name],
		[Timestamp]	
	FROM PresentationStatus 
	WHERE  [PresentationStatusID] = @PresentationStatusID 
	RETURN 
END'
exec(@fetchPresentationStatusProc)

/***********************************************************
*
* CRUD Procedures for [dbo].[PresentationType] table
*
************************************************************/
PRINT 'Creating stored procedures for PresentationType table'
-- Insert PresentationType
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[InsertPresentationType]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[InsertPresentationType]
END

DECLARE @insertPresentationTypeProc varchar(max)
SET @insertPresentationTypeProc = 
'CREATE PROCEDURE [dbo].[InsertPresentationType]
(
	@PresentationTypeID uniqueidentifier,
	@Name varchar(50),
	@newTimestamp timestamp output
)
AS
BEGIN
	INSERT INTO PresentationType
	(
		[PresentationTypeID],
		[Name]
	) 
	VALUES 
	(
		@PresentationTypeID,
		@Name
	)

	SELECT @newTimestamp = Timestamp 
	FROM PresentationType WHERE  [PresentationTypeID] = @PresentationTypeID 
	RETURN
END'
exec(@insertPresentationTypeProc)

-- Delete PresentationType
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DeletePresentationType]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[DeletePresentationType]
END

DECLARE @deletePresentationTypeProc varchar(max)
SET @deletePresentationTypeProc = 
'CREATE PROCEDURE [dbo].[DeletePresentationType]
(
	@PresentationTypeID uniqueidentifier
)
AS
BEGIN
	DELETE PresentationType
	WHERE PresentationTypeID=@PresentationTypeID
	RETURN
END'
exec(@deletePresentationTypeProc)

-- Update PresentationType
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[UpdatePresentationType]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[UpdatePresentationType]
END

DECLARE @updatePresentationTypeProc varchar(max)
SET @updatePresentationTypeProc = 
'CREATE PROCEDURE [dbo].[UpdatePresentationType]
(
	@PresentationTypeID uniqueidentifier,
	@Name varchar(50),
	@Timestamp timestamp,
	@newTimestamp timestamp output
)
AS
BEGIN
	UPDATE PresentationType 
	SET 
		[Name] = @Name
	WHERE  [PresentationTypeID] = @PresentationTypeID 
	  AND Timestamp = @TimeStamp

  IF @@ROWCOUNT = 0
    RAISERROR(''Row has been edited by another user'', 16, 1)            	

	SELECT @newTimeStamp = Timestamp
	FROM PresentationType WHERE  [PresentationTypeID] = @PresentationTypeID 
	RETURN 
END'
exec(@updatePresentationTypeProc)

-- Fetch PresentationType
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[FetchPresentationType]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[FetchPresentationType]
END

DECLARE @fetchPresentationTypeProc varchar(max)
SET @fetchPresentationTypeProc = 
'CREATE PROCEDURE [dbo].[FetchPresentationType]
(
	@PresentationTypeID uniqueidentifier
)
AS
BEGIN
	SELECT 
		[PresentationTypeID],
		[Name],
		[Timestamp]	
	FROM PresentationType 
	WHERE  [PresentationTypeID] = @PresentationTypeID 
	RETURN 
END'
exec(@fetchPresentationTypeProc)

/***********************************************************
*
* CRUD Procedures for [dbo].[Slide] table
*
************************************************************/
PRINT 'Creating stored procedures for Slide table'
-- Insert Slide
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[InsertSlide]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[InsertSlide]
END

DECLARE @insertSlideProc varchar(max)
SET @insertSlideProc = 
'CREATE PROCEDURE [dbo].[InsertSlide]
(
	@SlideID uniqueidentifier,
	@PresentationID uniqueidentifier,
	@Title varchar(100),
	@Location varchar(MAX),
	@Order int,
	@newTimestamp timestamp output
)
AS
BEGIN
	INSERT INTO Slide
	(
		[SlideID],
		[PresentationID],
		[Title],
		[Location],
		[Order]
	) 
	VALUES 
	(
		@SlideID,
		@PresentationID,
		@Title,
		@Location,
		@Order
	)

	SELECT @newTimestamp = Timestamp 
	FROM Slide WHERE  [SlideID] = @SlideID 
	RETURN
END'
exec(@insertSlideProc)

-- Delete Slide
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DeleteSlide]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[DeleteSlide]
END

DECLARE @deleteSlideProc varchar(max)
SET @deleteSlideProc = 
'CREATE PROCEDURE [dbo].[DeleteSlide]
(
	@SlideID uniqueidentifier
)
AS
BEGIN
	DELETE Slide
	WHERE SlideID=@SlideID
	RETURN
END'
exec(@deleteSlideProc)

-- Update Slide
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[UpdateSlide]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[UpdateSlide]
END

DECLARE @updateSlideProc varchar(max)
SET @updateSlideProc = 
'CREATE PROCEDURE [dbo].[UpdateSlide]
(
	@SlideID uniqueidentifier,
	@PresentationID uniqueidentifier,
	@Title varchar(100),
	@Location varchar(MAX),
	@Order int,
	@Timestamp timestamp,
	@newTimestamp timestamp output
)
AS
BEGIN
	UPDATE Slide 
	SET 
		[PresentationID] = @PresentationID,
		[Title] = @Title,
		[Location] = @Location,
		[Order] = @Order
	WHERE  [SlideID] = @SlideID 
	  AND Timestamp = @TimeStamp

  IF @@ROWCOUNT = 0
    RAISERROR(''Row has been edited by another user'', 16, 1)            	

	SELECT @newTimeStamp = Timestamp
	FROM Slide WHERE  [SlideID] = @SlideID 
	RETURN 
END'
exec(@updateSlideProc)

-- Fetch Slide
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[FetchSlide]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[FetchSlide]
END

DECLARE @fetchSlideProc varchar(max)
SET @fetchSlideProc = 
'CREATE PROCEDURE [dbo].[FetchSlide]
(
	@SlideID uniqueidentifier
)
AS
BEGIN
	SELECT 
		[SlideID],
		[PresentationID],
		[Title],
		[Location],
		[Order],
		[Timestamp]	
	FROM Slide 
	WHERE  [SlideID] = @SlideID 
	RETURN 
END'
exec(@fetchSlideProc)

PRINT 'Done creating stored procedures'
